{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14602 14602 14602 14602 14602 14602\n"
     ]
    }
   ],
   "source": [
    "#标准化字符串为json格式\n",
    "#国内疫情数据\n",
    "url='https://voice.baidu.com/newpneumonia/get?target=trend&isCaseIn=0&stage=publish&callback=jsonp_1593074577909_83653'\n",
    "#国外疫情数据\n",
    "url2='https://voice.baidu.com/newpneumonia/get?target=trend&isCaseIn=1&stage=publish&callback=jsonp_1593247926359_31143'\n",
    "text1='jsonp_1593074577909_83653('\n",
    "text2='jsonp_1593247926359_31143('\n",
    "def get_text(extra,url):\n",
    "    r=requests.get(url).text\n",
    "    r=r.replace(extra,\"\")\n",
    "    r=r[0:-2]\n",
    "    r=json.loads(r)\n",
    "    return r\n",
    "r=get_text(text2,url2)\n",
    "\n",
    "names=[]\n",
    "dates=[]\n",
    "dignose=[]\n",
    "heal=[]\n",
    "dead=[]\n",
    "add=[]\n",
    "#遍历每个地点\n",
    "for place in r['data']:\n",
    "#     print(place['name'])\n",
    "    length=len(place['trend']['updateDate'])\n",
    "    place_name=(place['name'] for i in range(length))\n",
    "    names.extend(place_name)            \n",
    "    #获取日期数据\n",
    "    dates.extend(place['trend']['updateDate'])\n",
    "    for trend in place['trend']['list']:\n",
    "        if(trend['name']=='确诊'):\n",
    "            dignose.extend(trend['data'])\n",
    "        if(trend['name']=='治愈'):\n",
    "            heal.extend(trend['data'])\n",
    "        if(trend['name']=='死亡'):\n",
    "            dead.extend(trend['data'])\n",
    "        if(trend['name']=='新增确诊'):\n",
    "            add.extend(trend['data'])  \n",
    "            \n",
    "#新增确诊数据如果存在缺失值，则将缺失值填充为0\n",
    "\n",
    "diff=len(names)-len(add)\n",
    "if(diff!=0):\n",
    "    add=add+[0 for i in range(diff)]\n",
    "    \n",
    "print(len(names),len(dates),len(dignose),len(heal),len(dead),len(add))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将数据保存为dataframe并保存到数据库中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def traslate(word):\n",
    "    '''\n",
    "    将世界各国的中文名转化为英文\n",
    "    '''\n",
    "    countries={\n",
    "   \"Somalia\": \"索马里\",\n",
    "  \"Liechtenstein\": \"列支敦士登\",\n",
    "  \"Morocco\": \"摩洛哥\",\n",
    "  \"W. Sahara\": \"西撒哈拉\",\n",
    "  \"Serbia\": \"塞尔维亚\",\n",
    "  \"Afghanistan\": \"阿富汗\",\n",
    "  \"Angola\": \"安哥拉\",\n",
    "  \"Albania\": \"阿尔巴尼亚\",\n",
    "  \"Andorra\": \"安道尔共和国\",\n",
    "  \"United Arab Emirates\": \"阿拉伯联合酋长国\",\n",
    "  \"Argentina\": \"阿根廷\",\n",
    "  \"Armenia\": \"亚美尼亚\",\n",
    "  \"Australia\": \"澳大利亚\",\n",
    "  \"Austria\": \"奥地利\",\n",
    "  \"Azerbaijan\": \"阿塞拜疆\",\n",
    "  \"Burundi\": \"布隆迪\",\n",
    "  \"Belgium\": \"比利时\",\n",
    "  \"Benin\": \"贝宁\",\n",
    "  \"Burkina Faso\": \"布基纳法索\",\n",
    "  \"Bangladesh\": \"孟加拉国\",\n",
    "  \"Bulgaria\": \"保加利亚\",\n",
    "  \"Bahrain\": \"巴林\",\n",
    "  \"Bahamas\": \"巴哈马\",\n",
    "  \"Bosnia and Herz.\": \"波斯尼亚和黑塞哥维那\",\n",
    "  \"Belarus\": \"白俄罗斯\",\n",
    "  \"Belize\": \"伯利兹\",\n",
    "  \"Bermuda\": \"百慕大\",\n",
    "  \"Bolivia\": \"玻利维亚\",\n",
    "  \"Brazil\": \"巴西\",\n",
    "  \"Barbados\": \"巴巴多斯\",\n",
    "  \"Brunei\": \"文莱\",\n",
    "  \"Bhutan\": \"不丹\",\n",
    "  \"Botswana\": \"博茨瓦纳\",\n",
    "  \"Central African Rep.\": \"中非\",\n",
    "  \"Canada\": \"加拿大\",\n",
    "  \"Switzerland\": \"瑞士\",\n",
    "  \"Chile\": \"智利\",\n",
    "  \"China\": \"中国\",\n",
    "  \"Côte d'Ivoire\": \"科特迪瓦\",\n",
    "  \"Cameroon\": \"喀麦隆\",\n",
    "  \"Dem. Rep. Congo\": \"刚果民主共和国\",\n",
    "  \"Congo\": \"刚果\",\n",
    "  \"Colombia\": \"哥伦比亚\",\n",
    "  \"Cape Verde\": \"佛得角\",\n",
    "  \"Costa Rica\": \"哥斯达黎加\",\n",
    "  \"Cuba\": \"古巴\",\n",
    "  \"N. Cyprus\": \"北塞浦路斯\",\n",
    "  \"Cyprus\": \"塞浦路斯\",\n",
    "  \"Czech Rep.\": \"捷克\",\n",
    "  \"Germany\": \"德国\",\n",
    "  \"Djibouti\": \"吉布提\",\n",
    "  \"Denmark\": \"丹麦\",\n",
    "  \"Dominican Rep.\": \"多米尼加\",\n",
    "  \"Algeria\": \"阿尔及利亚\",\n",
    "  \"Ecuador\": \"厄瓜多尔\",\n",
    "  \"Egypt\": \"埃及\",\n",
    "  \"Eritrea\": \"厄立特里亚\",\n",
    "  \"Spain\": \"西班牙\",\n",
    "  \"Estonia\": \"爱沙尼亚\",\n",
    "  \"Ethiopia\": \"埃塞俄比亚\",\n",
    "  \"Finland\": \"芬兰\",\n",
    "  \"Fiji\": \"斐济\",\n",
    "  \"France\": \"法国\",\n",
    "  \"Gabon\": \"加蓬\",\n",
    "  \"United Kingdom\": \"英国\",\n",
    "  \"Georgia\": \"格鲁吉亚\",\n",
    "  \"Ghana\": \"加纳\",\n",
    "  \"Guinea\": \"几内亚\",\n",
    "  \"Gambia\": \"冈比亚\",\n",
    "  \"Guinea-Bissau\": \"几内亚比绍\",\n",
    "  \"Eq. Guinea\": \"赤道几内亚\",\n",
    "  \"Greece\": \"希腊\",\n",
    "  \"Grenada\": \"格林纳达\",\n",
    "  \"Greenland\": \"格陵兰\",\n",
    "  \"Guatemala\": \"危地马拉\",\n",
    "  \"Guam\": \"关岛\",\n",
    "  \"Guyana\": \"圭亚那\",\n",
    "  \"Honduras\": \"洪都拉斯\",\n",
    "  \"Croatia\": \"克罗地亚\",\n",
    "  \"Haiti\": \"海地\",\n",
    "  \"Hungary\": \"匈牙利\",\n",
    "  \"Indonesia\": \"印度尼西亚\",\n",
    "  \"India\": \"印度\",\n",
    "  \"Br. Indian Ocean Ter.\": \"英属印度洋领土\",\n",
    "  \"Ireland\": \"爱尔兰\",\n",
    "  \"Iran\": \"伊朗\",\n",
    "  \"Iraq\": \"伊拉克\",\n",
    "  \"Iceland\": \"冰岛\",\n",
    "  \"Israel\": \"以色列\",\n",
    "  \"Italy\": \"意大利\",\n",
    "  \"Jamaica\": \"牙买加\",\n",
    "  \"Jordan\": \"约旦\",\n",
    "  \"Japan\": \"日本\",\n",
    "  \"Siachen Glacier\": \"锡亚琴冰川\",\n",
    "  \"Kazakhstan\": \"哈萨克斯坦\",\n",
    "  \"Kenya\": \"肯尼亚\",\n",
    "  \"Kyrgyzstan\": \"吉尔吉斯坦\",\n",
    "  \"Cambodia\": \"柬埔寨\",\n",
    "  \"Korea\": \"韩国\",\n",
    "  \"Kuwait\": \"科威特\",\n",
    "  \"Lao PDR\": \"老挝\",\n",
    "  \"Lebanon\": \"黎巴嫩\",\n",
    "  \"Liberia\": \"利比里亚\",\n",
    "  \"Libya\": \"利比亚\",\n",
    "  \"Sri Lanka\": \"斯里兰卡\",\n",
    "  \"Lesotho\": \"莱索托\",\n",
    "  \"Lithuania\": \"立陶宛\",\n",
    "  \"Luxembourg\": \"卢森堡\",\n",
    "  \"Latvia\": \"拉脱维亚\",\n",
    "  \"Moldova\": \"摩尔多瓦\",\n",
    "  \"Madagascar\": \"马达加斯加\",\n",
    "  \"Mexico\": \"墨西哥\",\n",
    "  \"Macedonia\": \"马其顿\",\n",
    "  \"Mali\": \"马里\",\n",
    "  \"Malta\": \"马耳他\",\n",
    "  \"Myanmar\": \"缅甸\",\n",
    "  \"Montenegro\": \"黑山\",\n",
    "  \"Mongolia\": \"蒙古\",\n",
    "  \"Mozambique\": \"莫桑比克\",\n",
    "  \"Mauritania\": \"毛里塔尼亚\",\n",
    "  \"Mauritius\": \"毛里求斯\",\n",
    "  \"Malawi\": \"马拉维\",\n",
    "  \"Malaysia\": \"马来西亚\",\n",
    "  \"Namibia\": \"纳米比亚\",\n",
    "  \"New Caledonia\": \"新喀里多尼亚\",\n",
    "  \"Niger\": \"尼日尔\",\n",
    "  \"Nigeria\": \"尼日利亚\",\n",
    "  \"Nicaragua\": \"尼加拉瓜\",\n",
    "  \"Netherlands\": \"荷兰\",\n",
    "  \"Norway\": \"挪威\",\n",
    "  \"Nepal\": \"尼泊尔\",\n",
    "  \"New Zealand\": \"新西兰\",\n",
    "  \"Oman\": \"阿曼\",\n",
    "  \"Pakistan\": \"巴基斯坦\",\n",
    "  \"Panama\": \"巴拿马\",\n",
    "  \"Peru\": \"秘鲁\",\n",
    "  \"Philippines\": \"菲律宾\",\n",
    "  \"Papua New Guinea\": \"巴布亚新几内亚\",\n",
    "  \"Poland\": \"波兰\",\n",
    "  \"Puerto Rico\": \"波多黎各\",\n",
    "  \"Dem. Rep. Korea\": \"朝鲜\",\n",
    "  \"Portugal\": \"葡萄牙\",\n",
    "  \"Paraguay\": \"巴拉圭\",\n",
    "  \"Palestine\": \"巴勒斯坦\",\n",
    "  \"Qatar\": \"卡塔尔\",\n",
    "  \"Romania\": \"罗马尼亚\",\n",
    "  \"Russia\": \"俄罗斯\",\n",
    "  \"Rwanda\": \"卢旺达\",\n",
    "  \"Saudi Arabia\": \"沙特阿拉伯\",\n",
    "  \"Sudan\": \"苏丹\",\n",
    "  \"S. Sudan\": \"南苏丹\",\n",
    "  \"Senegal\": \"塞内加尔\",\n",
    "  \"Singapore\": \"新加坡\",\n",
    "  \"Solomon Is.\": \"所罗门群岛\",\n",
    "  \"Sierra Leone\": \"塞拉利昂\",\n",
    "  \"El Salvador\": \"萨尔瓦多\",\n",
    "  \"Suriname\": \"苏里南\",\n",
    "  \"Slovakia\": \"斯洛伐克\",\n",
    "  \"Slovenia\": \"斯洛文尼亚\",\n",
    "  \"Sweden\": \"瑞典\",\n",
    "  \"Swaziland\": \"斯威士兰\",\n",
    "  \"Seychelles\": \"塞舌尔\",\n",
    "  \"Syria\": \"叙利亚\",\n",
    "  \"Chad\": \"乍得\",\n",
    "  \"Togo\": \"多哥\",\n",
    "  \"Thailand\": \"泰国\",\n",
    "  \"Tajikistan\": \"塔吉克斯坦\",\n",
    "  \"Turkmenistan\": \"土库曼斯坦\",\n",
    "  \"Timor-Leste\": \"东帝汶\",\n",
    "  \"Tonga\": \"汤加\",\n",
    "  \"Trinidad and Tobago\": \"特立尼达和多巴哥\",\n",
    "  \"Tunisia\": \"突尼斯\",\n",
    "  \"Turkey\": \"土耳其\",\n",
    "  \"Tanzania\": \"坦桑尼亚\",\n",
    "  \"Uganda\": \"乌干达\",\n",
    "  \"Ukraine\": \"乌克兰\",\n",
    "  \"Uruguay\": \"乌拉圭\",\n",
    "  \"United States\": \"美国\",\n",
    "  \"Uzbekistan\": \"乌兹别克斯坦\",\n",
    "  \"Venezuela\": \"委内瑞拉\",\n",
    "  \"Vietnam\": \"越南\",\n",
    "  \"Vanuatu\": \"瓦努阿图\",\n",
    "  \"Yemen\": \"也门\",\n",
    "  \"South Africa\": \"南非\",\n",
    "  \"Zambia\": \"赞比亚\",\n",
    "  \"Zimbabwe\": \"津巴布韦\",\n",
    "  \"Aland\": \"奥兰群岛\",\n",
    "  \"American Samoa\": \"美属萨摩亚\",\n",
    "  \"Fr. S. Antarctic Lands\": \"南极洲\",\n",
    "  \"Antigua and Barb.\": \"安提瓜和巴布达\",\n",
    "  \"Comoros\": \"科摩罗\",\n",
    "  \"Curaçao\": \"库拉索岛\",\n",
    "  \"Cayman Is.\": \"开曼群岛\",\n",
    "  \"Dominica\": \"多米尼加\",\n",
    "  \"Falkland Is.\": \"马尔维纳斯群岛（福克兰）\",\n",
    "  \"Faeroe Is.\": \"法罗群岛\",\n",
    "  \"Micronesia\": \"密克罗尼西亚\",\n",
    "  \"Heard I. and McDonald Is.\": \"赫德岛和麦克唐纳群岛\",\n",
    "  \"Isle of Man\": \"曼岛\",\n",
    "  \"Jersey\": \"泽西岛\",\n",
    "  \"Kiribati\": \"基里巴斯\",\n",
    "  \"Saint Lucia\": \"圣卢西亚\",\n",
    "  \"N. Mariana Is.\": \"北马里亚纳群岛\",\n",
    "  \"Montserrat\": \"蒙特塞拉特\",\n",
    "  \"Niue\": \"纽埃\",\n",
    "  \"Palau\": \"帕劳\",\n",
    "  \"Fr. Polynesia\": \"法属波利尼西亚\",\n",
    "  \"S. Geo. and S. Sandw. Is.\": \"南乔治亚岛和南桑威奇群岛\",\n",
    "  \"Saint Helena\": \"圣赫勒拿\",\n",
    "  \"St. Pierre and Miquelon\": \"圣皮埃尔和密克隆群岛\",\n",
    "  \"São Tomé and Principe\": \"圣多美和普林西比\",\n",
    "  \"Turks and Caicos Is.\": \"特克斯和凯科斯群岛\",\n",
    "  \"St. Vin. and Gren.\": \"圣文森特和格林纳丁斯\",\n",
    "  \"U.S. Virgin Is.\": \"美属维尔京群岛\",\n",
    "  \"Samoa\": \"萨摩亚\"\n",
    "    }\n",
    "    #中文-英文字典\n",
    "    c2e={}\n",
    "    for k,v in countries.items():\n",
    "        c2e[v]=k\n",
    "    try:\n",
    "        res=c2e[word]\n",
    "    except:\n",
    "        res=word\n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>疫情地区</th>\n",
       "      <th>日期</th>\n",
       "      <th>确诊</th>\n",
       "      <th>治愈</th>\n",
       "      <th>死亡</th>\n",
       "      <th>新增死亡</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>莱索托</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Lesotho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>莱索托</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Lesotho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>莱索托</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Lesotho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>莱索托</td>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Lesotho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>莱索托</td>\n",
       "      <td>2020-05-19</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Lesotho</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  疫情地区         日期 确诊 治愈 死亡  新增死亡     name\n",
       "0  莱索托 2020-05-12  1  0  0     1  Lesotho\n",
       "1  莱索托 2020-05-16  1  0  0     0  Lesotho\n",
       "2  莱索托 2020-05-17  1  0  0     0  Lesotho\n",
       "3  莱索托 2020-05-18  1  0  0     0  Lesotho\n",
       "4  莱索托 2020-05-19  1  0  0     0  Lesotho"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.DataFrame({\n",
    "    \"疫情地区\":names,\n",
    "    \"日期\":dates,\n",
    "    \"确诊\":dignose,\n",
    "    \"治愈\":heal,\n",
    "    \"死亡\":dead,\n",
    "    \"新增死亡\":add\n",
    "})\n",
    "df['日期']=['2020.'+i for i in df['日期']]\n",
    "df['日期']=df['日期'].str.replace(\".\",\"-\")\n",
    "df['日期']=pd.to_datetime(df['日期'])\n",
    "df['name']=df.疫情地区.apply(traslate)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_data(df,db_name,table_name,user,password):\n",
    "    conn = create_engine('mysql://{}:{}@localhost:3306/{}?charset=utf8'.format(user,password,db_name))\n",
    "    pd.io.sql.to_sql(df,table_name,con=conn,if_exists = 'replace',index=None)\n",
    "    df.head()\n",
    "save_data(df,'myspider','world_epidemic','root','123456')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取数据库中的数据获取需要显示的信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>疫情地区</th>\n",
       "      <th>日期</th>\n",
       "      <th>确诊</th>\n",
       "      <th>治愈</th>\n",
       "      <th>死亡</th>\n",
       "      <th>新增死亡</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>72</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北京</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>北京</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>102</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北京</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>111</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北京</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>121</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  疫情地区         日期   确诊  治愈  死亡  新增死亡\n",
       "0   北京 2020-01-26   72   2   0     4\n",
       "1   北京 2020-01-27   91   2   1    19\n",
       "2   北京 2020-01-28  102   4   1    11\n",
       "3   北京 2020-01-29  111   4   1    12\n",
       "4   北京 2020-01-30  121   5   1    18"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = create_engine('mysql://root:123456@localhost:3306/myspider?charset=utf8')\n",
    "epidemic=pd.read_sql('SELECT * FROM epidemic',con=conn)\n",
    "epidemic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "aaa=epidemic.groupby(\"日期\").sum()\n",
    "bbb=aaa.diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>确诊</th>\n",
       "      <th>治愈</th>\n",
       "      <th>死亡</th>\n",
       "      <th>新增死亡</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫情地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>9388419</td>\n",
       "      <td>7465261</td>\n",
       "      <td>513399</td>\n",
       "      <td>68084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>216371</td>\n",
       "      <td>185954</td>\n",
       "      <td>1025</td>\n",
       "      <td>1527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>184165</td>\n",
       "      <td>162004</td>\n",
       "      <td>2967</td>\n",
       "      <td>1195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>182486</td>\n",
       "      <td>159951</td>\n",
       "      <td>128</td>\n",
       "      <td>1164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>147922</td>\n",
       "      <td>131312</td>\n",
       "      <td>541</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>142643</td>\n",
       "      <td>125523</td>\n",
       "      <td>827</td>\n",
       "      <td>953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>134861</td>\n",
       "      <td>119694</td>\n",
       "      <td>139</td>\n",
       "      <td>904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>108870</td>\n",
       "      <td>94219</td>\n",
       "      <td>890</td>\n",
       "      <td>737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>103493</td>\n",
       "      <td>82949</td>\n",
       "      <td>1802</td>\n",
       "      <td>945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>92776</td>\n",
       "      <td>82290</td>\n",
       "      <td>0</td>\n",
       "      <td>623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>83694</td>\n",
       "      <td>72324</td>\n",
       "      <td>827</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>80077</td>\n",
       "      <td>62326</td>\n",
       "      <td>1111</td>\n",
       "      <td>825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>79972</td>\n",
       "      <td>68376</td>\n",
       "      <td>416</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>77937</td>\n",
       "      <td>65211</td>\n",
       "      <td>771</td>\n",
       "      <td>633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>48813</td>\n",
       "      <td>41887</td>\n",
       "      <td>129</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>46328</td>\n",
       "      <td>40711</td>\n",
       "      <td>816</td>\n",
       "      <td>336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>40012</td>\n",
       "      <td>34169</td>\n",
       "      <td>340</td>\n",
       "      <td>307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>36647</td>\n",
       "      <td>31260</td>\n",
       "      <td>276</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>26378</td>\n",
       "      <td>22708</td>\n",
       "      <td>257</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>24358</td>\n",
       "      <td>20948</td>\n",
       "      <td>0</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>24304</td>\n",
       "      <td>20933</td>\n",
       "      <td>417</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>24143</td>\n",
       "      <td>20612</td>\n",
       "      <td>803</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>22009</td>\n",
       "      <td>17091</td>\n",
       "      <td>113</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>20899</td>\n",
       "      <td>18250</td>\n",
       "      <td>273</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>20201</td>\n",
       "      <td>17215</td>\n",
       "      <td>236</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>18670</td>\n",
       "      <td>16291</td>\n",
       "      <td>280</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>16663</td>\n",
       "      <td>14187</td>\n",
       "      <td>184</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>10808</td>\n",
       "      <td>9021</td>\n",
       "      <td>384</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>10680</td>\n",
       "      <td>9702</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>香港</th>\n",
       "      <td>7722</td>\n",
       "      <td>4230</td>\n",
       "      <td>49</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>2667</td>\n",
       "      <td>2426</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>台湾</th>\n",
       "      <td>2319</td>\n",
       "      <td>795</td>\n",
       "      <td>38</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>澳门</th>\n",
       "      <td>253</td>\n",
       "      <td>111</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>148</td>\n",
       "      <td>134</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           确诊       治愈      死亡   新增死亡\n",
       "疫情地区                                 \n",
       "湖北    9388419  7465261  513399  68084\n",
       "广东     216371   185954    1025   1527\n",
       "河南     184165   162004    2967   1195\n",
       "浙江     182486   159951     128   1164\n",
       "湖南     147922   131312     541    950\n",
       "安徽     142643   125523     827    953\n",
       "江西     134861   119694     139    904\n",
       "山东     108870    94219     890    737\n",
       "黑龙江    103493    82949    1802    945\n",
       "江苏      92776    82290       0    623\n",
       "重庆      83694    72324     827    488\n",
       "北京      80077    62326    1111    825\n",
       "四川      79972    68376     416    535\n",
       "上海      77937    65211     771    633\n",
       "福建      48813    41887     129    334\n",
       "河北      46328    40711     816    336\n",
       "陕西      40012    34169     340    307\n",
       "广西      36647    31260     276    221\n",
       "云南      26378    22708     257    159\n",
       "山西      24358    20948       0    190\n",
       "天津      24304    20933     417    192\n",
       "海南      24143    20612     803    144\n",
       "内蒙古     22009    17091     113    231\n",
       "贵州      20899    18250     273    140\n",
       "辽宁      20201    17215     236    128\n",
       "甘肃      18670    16291     280    165\n",
       "吉林      16663    14187     184    150\n",
       "新疆      10808     9021     384     72\n",
       "宁夏      10680     9702       0     72\n",
       "香港       7722     4230      49    101\n",
       "青海       2667     2426       0     17\n",
       "台湾       2319      795      38     33\n",
       "澳门        253      111       0      1\n",
       "西藏        148      134       0      1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ccc=epidemic.groupby(\"疫情地区\").sum()\n",
    "ccc.sort_values(by='确诊',ascending=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
